package sm.pzavoli.mercato;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MercatoDBOpenHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "mercato.db";
    public static final String WATCHLIST_TABLE = "watchlist"; // Don't put word table in the name
    public static final String ACQUISTI_TABLE = "acquisti";
    public static final String STORICO_TABLE = "storico";
    // Column Names
    public static final String KEY_ID_TICKER = "_id_Ticker";
    public static final String KEY_ID_ACQUISTI = "_id_Acquisti";
    public static final String KEY_DATE = "date";
    public static final String KEY_DATE_STORICO = "datastorico";
    public static final String KEY_SYMBOL = "symbol";
    public static final String KEY_NOME = "nome";
    public static final String KEY_QUANTITY = "quantity";
    public static final String KEY_AVG_PRICE = "avg_price";
    public static final String KEY_OPEN = "open";
    public static final String KEY_CLOSE = "close";
    public static final String KEY_MIN = "minvalue";
    public static final String KEY_MAX = "maxvalue";
    public static final String KEY_VOLUME = "volume";

    private static final String WATCHLIST_CREATE =
            "create table " + WATCHLIST_TABLE + "(" + KEY_ID_TICKER + " integer primary key autoincrement, "
                    + KEY_DATE + " LONG default CURRENT_TIMESTAMP , "
                    + KEY_SYMBOL + " varchar(20),"  
                    + KEY_NOME + " varchar(30) );";

    private static final String ACQUISTI_CREATE =
            "create table " + ACQUISTI_TABLE + "(" + KEY_ID_TICKER + " integer, "
                    + KEY_DATE + " LONG default CURRENT_TIMESTAMP , "
                    + " quantity  LONG default 0 not null, "
                    + " price  float default 0 not null, "
                    + " primary key ("+KEY_ID_TICKER+","+KEY_DATE+"));";

	private static final String STORICO_CREATE = 
			"create table "+	STORICO_TABLE + " ("+KEY_ID_TICKER+" integer,"+
			KEY_DATE_STORICO+" long not null,"+
			" open real not null,"+
			" close real not null,"+
			" minvalue real not null,"+
			" maxvalue real not null,"+
			" volume real not null,"+
			" primary key ("+KEY_ID_TICKER+","+KEY_DATE_STORICO+"));";


	public MercatoDBOpenHelper(Context context, 
			CursorFactory factory) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	public MercatoDBOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	public MercatoDBOpenHelper(Context context, String name,
			CursorFactory factory, int version,
			DatabaseErrorHandler errorHandler) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION, errorHandler);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
        Log.i(this.getClass().getName(), "Create databases");		
        db.execSQL(WATCHLIST_CREATE);
        db.execSQL(ACQUISTI_CREATE);
        db.execSQL(STORICO_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(this.getClass().getName(), "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + WATCHLIST_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + ACQUISTI_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + STORICO_TABLE);
        onCreate(db);
	}

}
